package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

import model.Item;
import model.Project;
import model.ProjectUsedItem;

public class DBProjectUsedItem implements IFProjectUsedItem 
{
	private Connection con;

	public DBProjectUsedItem()
	{

		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int addProjectUsedItem(Project project, Item item, int quantity) throws SQLException
	{
		int rc = -1;
		String query = "SELECT pui_id FROM project_used_item WHERE pui_item_id = ? AND pui_project_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("addProjectUsedItem", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, item.getItemID());
			ps.setInt(2, project.getServiceID());

			ResultSet results = ps.executeQuery();

			if (!results.next())
			{
				query = "INSERT INTO project_used_item(pui_quantity, pui_item_id, pui_project_id) VALUES (?, ?, ?)";

				if (DBConnection.doDebug())
					DBConnection.printDebug("addProjectUsedItem", query);

				try
				{
					IFItem dbItem = new DBItem();
					dbItem.updateItemQuantity(item.getItemID(), 0, quantity);

					ps = con.prepareStatement(query);

					ps.setInt(1, quantity);
					ps.setInt(2, item.getItemID());
					ps.setInt(3, project.getServiceID());

					rc = ps.executeUpdate();
				}
				catch(Exception e)
				{
					if (DBConnection.doDebug())
					{
						DBConnection.printDebug("addProjectUsedItem", query);
						e.printStackTrace();
					} 
				}
			}

			ps.close();
		} 
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				DBConnection.printDebug("addProjectUsedItem", query);
				e.printStackTrace();
			} 
		}

		return rc;
	}

	@Override
	public int deleteProjectUsedItem(Project project, int itemID) throws SQLException
	{
		int rc = -1;
		String query = "SELECT pui_id, pui_quantity FROM project_used_item WHERE pui_item_id = ? AND pui_project_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("deleteProjectUsedItem", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);
			ps.setInt(2, project.getServiceID());

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				query = "DELETE FROM project_used_item WHERE pui_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("deleteProjectUsedItem", query);

				try
				{
					ps = con.prepareStatement(query);

					ps.setInt(1, results.getInt("pui_id"));

					rc = ps.executeUpdate();

					IFItem dbItem = new DBItem();
					dbItem.updateItemQuantity(itemID, results.getInt("pui_quantity"), 0 );
				}
				catch(Exception e)
				{
					if (DBConnection.doDebug())
					{
						DBConnection.printDebug("deleteProjectUsedItem", query);
						e.printStackTrace();
					} 
				}
			}

			ps.close();
		} 
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				DBConnection.printDebug("deleteProjectUsedItem", query);
				e.printStackTrace();
			} 
		}

		return rc;
	}

	@Override
	public int updateProjectUsedItem(Project project, int itemID, int quantity) throws SQLException
	{
		int rc = -1;
		String query = "SELECT pui_id, pui_quantity FROM project_used_item WHERE pui_item_id = ? AND pui_project_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("updateProjectUsedItem", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, itemID);
			ps.setInt(2, project.getServiceID());

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				query = "UPDATE project_used_item SET pui_quantity = ? WHERE pui_id = ?";

				if (DBConnection.doDebug())
					DBConnection.printDebug("updateProjectUsedItem", query);

				try
				{
					ps = con.prepareStatement(query);

					ps.setInt(1, quantity);
					ps.setInt(2, results.getInt("pui_id"));

					rc = ps.executeUpdate();

					IFItem dbItem = new DBItem();
					dbItem.updateItemQuantity(itemID, results.getInt("pui_quantity"), quantity);
				}
				catch(Exception e)
				{
					if (DBConnection.doDebug())
					{
						DBConnection.printDebug("updateProjectUsedItem", query);
						e.printStackTrace();
					} 
				}
			}

			ps.close();
		} 
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				DBConnection.printDebug("updateProjectUsedItem", query);
				e.printStackTrace();
			} 
		}

		return rc;
	}

	@Override
	public ArrayList<ProjectUsedItem> getAllProjectUsedItem(Project project) 
	{
		ArrayList<ProjectUsedItem> itemList = new ArrayList<ProjectUsedItem>();

		String query = "SELECT pui.pui_quantity, i.item_id, i.item_name, i.item_price FROM project_used_item AS pui LEFT JOIN item AS i ON pui.pui_item_id = i.item_id WHERE pui.pui_project_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getAllProjectUsedItem", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, project.getServiceID());

			ResultSet result = ps.executeQuery();

			while(result.next())
			{
				ProjectUsedItem newProjectUsedItem = new ProjectUsedItem(result.getInt("pui_quantity"), new Item(result.getInt("item_id"), result.getString("item_name"), result.getDouble("item_price")));

				itemList.add(newProjectUsedItem);
			}

			ps.close();
		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("getAllProjectUsedItem", query);
				e.printStackTrace();
			}

		}
		return itemList;
	}



	@Override
	public double getTotalMaterialPrice(int projectID) 
	{
		double totalItemPrice = 0.0;

		String query = "SELECT SUM(pui.pui_quantity*i.item_price) AS total_item_price FROM project_used_item AS pui LEFT JOIN item AS i ON pui.pui_item_id = i.item_id WHERE pui.pui_project_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getTotalMaterialPrice", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, projectID);

			ResultSet result = ps.executeQuery();

			if (result.next())
			{
				totalItemPrice = result.getDouble("total_item_price");
			}

			ps.close();
		} 
		catch (Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("addProjectUsedItem", query);
				e.printStackTrace();
			} 
		}

		return totalItemPrice;
	}
}